Skip Headers

Oracle® Streams Advanced Queuing User's Guide and Reference 10g Release 2 (10.2) Part Number B14257-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 9 Oracle Streams AQ Views

This chapter describes the Oracle Streams Advanced Queuing (AQ) administrative interface views.

This chapter contains these topics:

- DBA_QUEUE_TABLES: All Queue Tables in Database

- ALL_QUEUE_TABLES: User Queue Tables

- DBA_QUEUES: All Queues in Database

- DBA_QUEUE_SCHEDULES: All Propagation Schedules

- ALL_QUEUES: Queues for Which User Has Any Privilege

- QUEUE_PRIVILEGES: Queues for Which User Has Queue Privilege

- AQ$Queue_Table_Name: Messages in Queue Table

- USER_QUEUE_TABLES: Queue Tables in User Schema

- USER_QUEUES: Queues In User Schema

- USER_QUEUE_SCHEDULES: Propagation Schedules in User Schema

- AQ$Queue_Table_Name_S: Queue Subscribers

- AQ$Queue_Table_Name_R: Queue Subscribers and Their Rules

- DBA_QUEUE_SUBSCRIBERS: All Queue Subscribers in Database

- USER_QUEUE_SUBSCRIBERS: Queue Subscribers in User Schema

- ALL_QUEUE_SUBSCRIBERS: Subscribers for Queues Where User Has Queue Privileges

- GV$AQ: Number of Messages in Different States in Database

- V$AQ: Number of Messages in Different States for Specific Instances

- AQ$INTERNET_USERS: Oracle Streams AQ Agents Registered for Internet Access

- DBA_TRANSFORMATIONS: All Transformations

- DBA_ATTRIBUTE_TRANSFORMATIONS: All Transformation Functions

- USER_TRANSFORMATIONS: User Transformations

- USER_ATTRIBUTE_TRANSFORMATIONS: User Transformation Functions

## 9.1 DBA_QUEUE_TABLES: All Queue Tables in Database

The `DBA_QUEUE_TABLES` view describes the names and types of all queue tables created in the database.

Table 9-1 DBA_QUEUE_TABLES View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`OWNER` | `VARCHAR2(30)` | - | Queue table schema
`QUEUE_TABLE` | `VARCHAR2(30)` | - | Queue table name
`TYPE` | `VARCHAR2(7)` | - | Payload type
`OBJECT_TYPE` | `VARCHAR2(61)` | - | Name of object type, if any
`SORT_ORDER` | `VARCHAR2(22)` | - | User-specified sort order
`RECIPIENTS` | `VARCHAR2(8)` | - | `SINGLE` or `MULTIPLE`
`MESSAGE_GROUPING` | `VARCHAR2(13)` | - | `NONE` or `TRANSACTIONAL`
`COMPATIBLE` | `VARCHAR2(5)` | - | Indicates the lowest version with which the queue table is compatible
`PRIMARY_INSTANCE` | `NUMBER` | - | Indicates which instance is the primary owner of the queue table, or no primary owner if 0
`SECONDARY_INSTANCE` | `NUMBER` | - | Indicates which instance is the secondary owner of the queue table. This instance becomes the owner of the queue table if the primary owner is not up. A value of 0 indicates that there is no secondary owner.
`OWNER_INSTANCE` | `NUMBER` | - | Indicates which instance currently owns the queue table
`USER_COMMENT` | `VARCHAR2(50)` | - | User comment for the queue table
`SECURE` | `VARCHAR2(3)` | - | Indicates whether this queue table is secure (`YES`) or not (`NO`). Secure queues are queues for which AQ agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue. The owner of a secure queue can perform all queue operations on the queue, but other users cannot perform queue operations on a secure queue, unless they are configured as secure queue users.

See Also: Oracle Streams Concepts and Administration for more information on secure queues.

## 9.2 ALL_QUEUE_TABLES: User Queue Tables

The `ALL_QUEUE_TABLES` view describes queue tables accessible to a user.

Table 9-2 ALL_QUEUE_TABLES View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`OWNER` | `VARCHAR2(30)` | - | Owner of the queue table
`QUEUE_TABLE` | `VARCHAR2(30)` | - | Queue table name
`TYPE` | `VARCHAR2(7)` | - | Payload type
`OBJECT_TYPE` | `VARCHAR2(61)` | - | Name of object type, if any
`SORT_ORDER` | `VARCHAR2(22)` | - | User-specified sort order
`RECIPIENTS` | `VARCHAR2(8)` | - | `SINGLE` or `MULTIPLE`
`MESSAGE_GROUPING` | `VARCHAR2(13)` | - | `NONE` or `TRANSACTIONAL`
`COMPATIBLE` | `VARCHAR2(5)` | - | Indicates the lowest version with which the queue table is compatible
`PRIMARY_INSTANCE` | `NUMBER` | - | Indicates which instance is the primary owner of the queue table, or no primary owner if 0
`SECONDARY_INSTANCE` | `NUMBER` | - | Indicates which instance is the secondary owner of the queue table. This instance becomes the owner of the queue table if the primary owner is not up. A value of 0 indicates that there is no secondary owner.
`OWNER_INSTANCE` | `NUMBER` | - | Indicates which instance currently owns the queue table
`USER_COMMENT` | `VARCHAR2(50)` | - | User comment for the queue table
`SECURE` | `VARCHAR2(3)` | - | Indicates whether this queue table is secure (`YES`) or not (`NO`). Secure queues are queues for which AQ agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue. The owner of a secure queue can perform all queue operations on the queue, but other users cannot perform queue operations on a secure queue, unless they are configured as secure queue users.

## 9.3 DBA_QUEUES: All Queues in Database

The `DBA_QUEUES` view specifies operational characteristics for every queue in a database.

Table 9-3 DBA_QUEUES View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`OWNER` | `VARCHAR2(30)` | `NOT NULL` | Queue schema name
`NAME` | `VARCHAR2(30)` | `NOT NULL` | Queue name
`QUEUE_TABLE` | `VARCHAR2(30)` | `NOT NULL` | Queue table where this queue resides
`QID` | `NUMBER` | `NOT NULL` | Unique queue identifier
`QUEUE_TYPE` | `VARCHAR2(20)` | - | Queue type
`MAX_RETRIES` | `NUMBER` | - | Number of dequeue attempts allowed
`RETRY_DELAY` | `NUMBER` | - | Number of seconds before retry can be attempted
`ENQUEUE_ENABLED` | `VARCHAR2(7)` | - | `YES` or `NO`
`DEQUEUE_ENABLED` | `VARCHAR2(7)` | - | `YES` or `NO`
`RETENTION` | `VARCHAR2(40)` | - | Number of seconds message is retained after dequeue
`USER_COMMENT` | `VARCHAR2(50)` | - | User comment for the queue
`NETWORK_NAME` | `VARCHAR2(64)` | - | Network name for a queue

Note: A message is moved to an exception queue if `RETRY_COUNT` is greater than `MAX_RETRIES`. If a dequeue transaction fails because the server process dies (including `ALTER` `SYSTEM` `KILL` `SESSION`) or `SHUTDOWN` `ABORT` on the instance, then `RETRY_COUNT` is not incremented.

## 9.4 DBA_QUEUE_SCHEDULES: All Propagation Schedules

The `DBA_QUEUE_SCHEDULES` view describes the current schedules for propagating messages.

Table 9-4 DBA_QUEUE_SCHEDULES View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`SCHEMA` | `VARCHAR2(30)` | `NOT NULL` | Schema name for the source queue
`QNAME` | `VARCHAR2(30)` | `NOT NULL` | Source queue name
`DESTINATION` | `VARCHAR2(128)` | `NOT NULL` | Destination name, currently limited to be a database link name
`START_DATE` | `DATE` | - | Date to start propagation in the default date format
`START_TIME` | `VARCHAR2(8)` | - | Time of day to start propagation in HH:MI:SS format
`PROPAGATION_WINDOW` | `NUMBER` | - | Duration in seconds for the propagation window
`NEXT_TIME` | `VARCHAR2(200)` | - | Function to compute the start of the next propagation window
`LATENCY` | `NUMBER` | - | Maximum wait time to propagate a message during the propagation window
`SCHEDULE_DISABLED` | `VARCHAR(1)` | - | `N` if enabled; `Y` if disabled (schedule will not be executed)
`PROCESS_NAME` | `VARCHAR2(8)` | - | Name of Jnnn background process executing this schedule; `NULL` if not currently executing
`SESSION_ID` | `VARCHAR2(82)` | - | Session ID (SID, SERIAL#) of the job executing this schedule; `NULL` if not currently executing
`INSTANCE` | `NUMBER` | - | RAC instance number executing this schedule
`LAST_RUN_DATE` | `DATE` | - | Date of the last successful execution
`LAST_RUN_TIME` | `VARCHAR2(8)` | - | Time of the last successful execution in HH:MI:SS format
`CURRENT_START_DATE` | `DATE` | - | Date the current window of this schedule was started
`CURRENT_START_TIME` | `VARCHAR2(8)` | - | Time the current window of this schedule was started in HH:MI:SS format
`NEXT_RUN_DATE` | `DATE` | - | Date the next window of this schedule will be started
`NEXT_RUN_TIME` | `VARCHAR2(8)` | - | Time the next window of this schedule will be started in HH:MI:SS format
`TOTAL_TIME` | `NUMBER` | - | Total time in seconds spent in propagating messages from the schedule
`TOTAL_NUMBER` | `NUMBER` | - | Total number of messages propagated in this schedule
`TOTAL_BYTES` | `NUMBER` | - | Total number of bytes propagated in this schedule
`MAX_NUMBER` | `NUMBER` | - | Maximum number of messages propagated in a propagation window
`MAX_BYTES` | `NUMBER` | - | Maximum number of bytes propagated in a propagation window
`AVG_NUMBER` | `NUMBER` | - | Average number of messages propagated in a propagation window
`AVG_SIZE` | `NUMBER` | - | Average size of propagated messages in bytes
`AVG_TIME` | `NUMBER` | - | Average time to propagate a message in seconds
`FAILURES` | `NUMBER` | - | Number of times execution failed. If it reaches 16, then the schedule is disabled.
`LAST_ERROR_DATE` | `DATE` | - | Date of the last unsuccessful execution
`LAST_ERROR_TIME` | `VARCHAR2(8)` | - | Time of the last unsuccessful execution in HH:MI:SS format
`LAST_ERROR_MSG` | `VARCHAR2(4000)` | - | Error number and error message text of the last unsuccessful execution
`MESSAGE_DELIVERY_MODE` | `VARCHAR2(10)` | - | `PERSISTENT` for persistent messages or `BUFFERED` for buffered messages

## 9.5 ALL_QUEUES: Queues for Which User Has Any Privilege

The `ALL_QUEUES` view describes all queues accessible to the user.

Table 9-5 ALL_QUEUES View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`OWNER` | `VARCHAR2(30)` | `NOT NULL` | Owner of the queue
`NAME` | `VARCHAR2(30)` | `NOT NULL` | Name of the queue
`QUEUE_TABLE` | `VARCHAR2(30)` | `NOT NULL` | Queue table name
`QID` | `NUMBER` | `NOT NULL` | Unique queue identifier
`QUEUE_TYPE` | `VARCHAR2(15)` | - | Queue type
`MAX_RETRIES` | `NUMBER` | - | Number of dequeue attempts allowed
`RETRY_DELAY` | `NUMBER` | - | Number of seconds before retry can be attempted
`ENQUEUE_ENABLED` | `VARCHAR2(7)` | - | `YES` or `NO`
`DEQUEUE_ENABLED` | `VARCHAR2(7)` | - | `YES` or `NO`
`RETENTION` | `VARCHAR2(40)` | - | Number of seconds message is retained after dequeue
`USER_COMMENT` | `VARCHAR2(50)` | - | User comment for the queue

Note: A message is moved to an exception queue if `RETRY_COUNT` is greater than `MAX_RETRIES`. If a dequeue transaction fails because the server process dies (including `ALTER` `SYSTEM` `KILL` `SESSION`) or `SHUTDOWN` `ABORT` on the instance, then `RETRY_COUNT` is not incremented.

## 9.6 QUEUE_PRIVILEGES: Queues for Which User Has Queue Privilege

The `QUEUE_PRIVILEGES` view describes queues for which the user is the grantor, grantee, or owner. It also shows queues for which an enabled role on the queue is granted to `PUBLIC`.

Table 9-6 QUEUE_PRIVILEGES View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`GRANTEE` | `VARCHAR2(30)` | `NOT NULL` | Name of the user to whom access was granted
`OWNER` | `VARCHAR2(30)` | `NOT NULL` | Owner of the queue
`NAME` | `VARCHAR2(30)` | `NOT NULL` | Name of the queue
`GRANTOR` | `VARCHAR2(30)` | `NOT NULL` | Name of the user who performed the grant
`ENQUEUE_PRIVILEGE` | `NUMBER` | - | Permission to enqueue to queue (`1` if granted, `0` if not)
`DEQUEUE_PRIVILEGE` | `NUMBER` | - | Permission to dequeue from queue (`1` if granted, `0` if not)

## 9.7 AQ$Queue_Table_Name: Messages in Queue Table

The `AQ$``Queue_Table_Name` view describes the queue table in which message data is stored. This view is automatically created with each queue table and should be used for querying the queue data. The dequeue history data (time, user identification and transaction identification) is only valid for single-consumer queues.

In a queue table that is created with the `compatible` parameter set to '8.1' or higher, messages that were not dequeued by the consumer are shown as "`UNDELIVERABLE`". You can dequeue these messages by `msgid`. If the Oracle Streams AQ queue process monitor is running, then the messages are eventually moved to an exception queue. You can dequeue these messages from the exception queue with an ordinary dequeue.

A multiconsumer queue table created without the `compatible` parameter, or with the `compatible` parameter set to '8.0', does not display the state of a message on a consumer basis, but only displays the global state of the message.

Note: Queues created in a queue table with `compatible` set to `8.0` (referrred to in this guide as 8.0-style queues) are deprecated in Oracle Streams AQ 10g Release 2 (10.2). Oracle recommends that any new queues you create be 8.1-style or newer and that you migrate existing 8.0-style queues at your earliest convenience.

When a message is dequeued using the `REMOVE` mode, `DEQ_TIME`, `DEQ_USER_ID`, and `DEQ_TXN_ID` are updated for the consumer that dequeued the message.

You can use `MSGID` and `ORIGINAL_MSGID` to chain propagated messages. When a message with message identifier `m1` is propagated to a remote queue, `m1` is stored in the `ORIGINAL_MSGID` column of the remote queue.

Beginning with Oracle Database 10g, `AQ$``Queue_Table_Name` includes buffered messages. For buffered messages, the value of `MSG_STATE` is one of the following:

- `IN MEMORY`

  Buffered messages enqueued by a user

- `DEFERRED`

  Buffered messages enqueued by a capture process

- `SPILLED`

  User-enqueued buffered messages that have been spilled to disk

- `DEFERRED SPILLED`

  Capture-enqueued buffered messages that have been spilled to disk

- `BUFFERED EXPIRED`

  Expired buffered messages

Table 9-7 AQ$Queue_Table_Name View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`QUEUE` | `VARCHAR2(30)` | - | Queue name
`MSG_ID` | `RAW(16)` | `NOT NULL` | Unique identifier of the message
`CORR_ID` | `VARCHAR2(128)` | - | User-provided correlation identifier
`MSG_PRIORITY` | `NUMBER` | - | Message priority
`MSG_STATE` | `VARCHAR2(16)` | - | Message state
`DELAY` | `DATE` | - | Number of seconds the message is delayed
`DELAY_TIMESTAMP` | `TIMESTAMP` | - | Number of seconds the message is delayed
`EXPIRATION` | `NUMBER` | - | Number of seconds in which the message expires after being `READY`
`ENQ_TIME` | `DATE` | - | Enqueue time
`ENQ_TIMESTAMP` | `TIMESTAMP` | - | Enqueue time
`ENQ_USER_ID` (8.0.4 or 8.1.3 queue tables) | `NUMBER` | - | Enqueue user ID
`ENQ_USER_ID` (10.1 queue tables) | `VARCHAR2(30)` | - | Enqueue user ID
`ENQ_TXN_ID` | `VARCHAR2(30)` | - | Enqueue transaction ID
`DEQ_TIME` | `DATE` | - | Dequeue time
`DEQ_TIMESTAMP` | `TIMESTAMP` | - | Dequeue time
`DEQ_USER_ID` (8.0.4 or 8.1.3 queue tables) | `NUMBER` | - | Dequeue user ID
`DEQ_USER_ID` (10.1 queue tables) | `VARCHAR2(30)` | - | Dequeue user ID
`DEQ_TXN_ID` | `VARCHAR2(30)` | - | Dequeue transaction ID
`RETRY_COUNT` | `NUMBER` | - | Number of retries
`EXCEPTION_QUEUE_OWNER` | `VARCHAR2(30)` | - | Exception queue schema
`EXCEPTION_QUEUE` | `VARCHAR2(30)` | - | Exception queue name
`USER_DATA` | - | - | User data
`SENDER_NAME` | `VARCHAR2(30)` | - | Name of the agent enqueuing the message (valid only for 8.1-compatible queue tables)
`SENDER_ADDRESS` | `VARCHAR2(1024)` | - | Queue name and database name of the source (last propagating) queue (valid only for 8.1-compatible queue tables). The database name is not specified if the source queue is in the local database.
`SENDER_PROTOCOL` | `NUMBER` | - | Protocol for sender address (reserved for future use and valid only for 8.1-compatible queue tables)
`ORIGINAL_MSGID` | `RAW(16)` | - | Message ID of the message in the source queue (valid only for 8.1-compatible queue tables)
`CONSUMER_NAME` | `VARCHAR2(30)` | - | Name of the agent receiving the message (valid only for 8.1-compatible multiconsumer queue tables)
`ADDRESS` | `VARCHAR2(1024)` | - | Queue name and database link name of the agent receiving the message.The database link name is not specified if the address is in the local database. The address is `NULL` if the receiving agent is local to the queue (valid only for 8.1-compatible multiconsumer queue tables)
`PROTOCOL` | `NUMBER` | - | Protocol for address of receiving agent (valid only for 8.1-compatible queue tables)
`PROPAGATED_MSGID` | `RAW(16)` | - | Message ID of the message in the queue of the receiving agent (valid only for 8.1-compatible queue tables)
`ORIGINAL_QUEUE_NAME` | `VARCHAR2(30)` | - | Name of the queue the message came from
`ORIGINAL_QUEUE_OWNER` | `VARCHAR2(30)` | - | Owner of the queue the message came from
`EXPIRATION_REASON` | `VARCHAR2(19)` | - | Reason the message came into exception queue. Possible values are `TIME_EXPIRATION` (message expired after the specified expired time), `MAX_RETRY_EXCEEDED` (maximum retry count exceeded), and `PROPAGATION_FAILURE` (message became undeliverable during propagation).

Note: A message is moved to an exception queue if `RETRY_COUNT` is greater than `MAX_RETRIES`. If a dequeue transaction fails because the server process dies (including `ALTER` `SYSTEM` `KILL` `SESSION`) or `SHUTDOWN` `ABORT` on the instance, then `RETRY_COUNT` is not incremented.

## 9.8 USER_QUEUE_TABLES: Queue Tables in User Schema

The `USER_QUEUE_TABLES` view is the same as `DBA_QUEUE_TABLES` with the exception that it only shows queue tables in the user's schema. It does not contain a column for `OWNER`.

Table 9-8 USER_QUEUE_TABLES View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`QUEUE_TABLE` | `VARCHAR2(30)` | - | Queue table name
`TYPE` | `VARCHAR2(7)` | - | Payload type
`OBJECT_TYPE` | `VARCHAR2(61)` | - | Name of object type, if any
`SORT_ORDER` | `VARCHAR2(22)` | - | User-specified sort order
`RECIPIENTS` | `VARCHAR2(8)` | - | `SINGLE` or `MULTIPLE`
`MESSAGE_GROUPING` | `VARCHAR2(13)` | - | `NONE` or `TRANSACTIONAL`
`COMPATIBLE` | `VARCHAR2(5)` | - | Indicates the lowest version with which the queue table is compatible
`PRIMARY_INSTANCE` | `NUMBER` | - | Indicates which instance is the primary owner of the queue table, or no primary owner if 0
`SECONDARY_INSTANCE` | `NUMBER` | - | Indicates which instance is the secondary owner of the queue table. This instance becomes the owner of the queue table if the primary owner is not up. A value of 0 indicates that there is no secondary owner.
`OWNER_INSTANCE` | `NUMBER` | - | Indicates which instance currently owns the queue table
`USER_COMMENT` | `VARCHAR2(50)` | - | User comment for the queue table
`SECURE` | `VARCHAR2(3)` | - | Indicates whether this queue table is secure (`YES`) or not (`NO`). Secure queues are queues for which AQ agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue. The owner of a secure queue can perform all queue operations on the queue, but other users cannot perform queue operations on a secure queue, unless they are configured as secure queue users.

## 9.9 USER_QUEUES: Queues In User Schema

The `USER_QUEUES` view is the same as `DBA_QUEUES` with the exception that it only shows queues in the user's schema.

Table 9-9 USER_QUEUES View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`NAME` | `VARCHAR2(30)` | `NOT NULL` | Queue name
`QUEUE_TABLE` | `VARCHAR2(30)` | `NOT NULL` | Queue table where this queue resides
`QID` | `NUMBER` | `NOT NULL` | Unique queue identifier
`QUEUE_TYPE` | `VARCHAR2(20)` | - | Queue type
`MAX_RETRIES` | `NUMBER` | - | Number of dequeue attempts allowed
`RETRY_DELAY` | `NUMBER` | - | Number of seconds before retry can be attempted
`ENQUEUE_ENABLED` | `VARCHAR2(7)` | - | `YES` or `NO`
`DEQUEUE_ENABLED` | `VARCHAR2(7)` | - | `YES` or `NO`
`RETENTION` | `VARCHAR2(40)` | - | Number of seconds message is retained after dequeue
`USER_COMMENT` | `VARCHAR2(50)` | - | User comment for the queue
`NETWORK_NAME` | `VARCHAR2(64)` | - | Network name for a queue

Note: A message is moved to an exception queue if `RETRY_COUNT` is greater than `MAX_RETRIES`. If a dequeue transaction fails because the server process dies (including `ALTER` `SYSTEM` `KILL` `SESSION`) or `SHUTDOWN` `ABORT` on the instance, then `RETRY_COUNT` is not incremented.

## 9.10 USER_QUEUE_SCHEDULES: Propagation Schedules in User Schema

The `USER_QUEUE_SCHEDULES` view is the same as `DBA_QUEUE_SCHEDULES` with the exception that it only shows queue schedules in the user's schema.

Table 9-10 DBA_QUEUE_SCHEDULES View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`QNAME` | `VARCHAR2(30)` | `NOT NULL` | Source queue name
`DESTINATION` | `VARCHAR2(128)` | `NOT NULL` | Destination name, currently limited to be a database link name
`START_DATE` | `DATE` | - | Date to start propagation in the default date format
`START_TIME` | `VARCHAR2(8)` | - | Time of day to start propagation in HH:MI:SS format
`PROPAGATION_WINDOW` | `NUMBER` | - | Duration in seconds for the propagation window
`NEXT_TIME` | `VARCHAR2(200)` | - | Function to compute the start of the next propagation window
`LATENCY` | `NUMBER` | - | Maximum wait time to propagate a message during the propagation window
`SCHEDULE_DISABLED` | `VARCHAR(1)` | - | `N` if enabled; `Y` if disabled (schedule will not be executed)
`PROCESS_NAME` | `VARCHAR2(8)` | - | Name of `Jnnn` background process executing this schedule; `NULL` if not currently executing
`SESSION_ID` | `VARCHAR2(82)` | - | Session ID (SID, SERIAL#) of the job executing this schedule; `NULL` if not currently executing
`INSTANCE` | `NUMBER` | - | Real Application Clusters instance number executing this schedule
`LAST_RUN_DATE` | `DATE` | - | Date of the last successful execution
`LAST_RUN_TIME` | `VARCHAR2(8)` | - | Time of the last successful execution in HH:MI:SS format
`CURRENT_START_DATE` | `DATE` | - | Date the current window of this schedule was started
`CURRENT_START_TIME` | `VARCHAR2(8)` | - | Time the current window of this schedule was started in HH:MI:SS format
`NEXT_RUN_DATE` | `DATE` | - | Date the next window of this schedule will be started
`NEXT_RUN_TIME` | `VARCHAR2(8)` | - | Time the next window of this schedule will be started in HH:MI:SS format
`TOTAL_TIME` | `NUMBER` | - | Total time in seconds spent in propagating messages from the schedule
`TOTAL_NUMBER` | `NUMBER` | - | Total number of messages propagated in this schedule
`TOTAL_BYTES` | `NUMBER` | - | Total number of bytes propagated in this schedule
`MAX_NUMBER` | `NUMBER` | - | Maximum number of messages propagated in a propagation window
`MAX_BYTES` | `NUMBER` | - | Maximum number of bytes propagated in a propagation window
`AVG_NUMBER` | `NUMBER` | - | Average number of messages propagated in a propagation window
`AVG_SIZE` | `NUMBER` | - | Average size of propagated messages in bytes
`AVG_TIME` | `NUMBER` | - | Average time to propagate a message in seconds
`FAILURES` | `NUMBER` | - | Number of times execution failed. If it reaches 16, then the schedule is disabled.
`LAST_ERROR_DATE` | `DATE` | - | Date of the last unsuccessful execution
`LAST_ERROR_TIME` | `VARCHAR2(8)` | - | Time of the last unsuccessful execution in HH:MI:SS format
`LAST_ERROR_MSG` | `VARCHAR2(4000)` | - | Error number and error message text of the last unsuccessful execution

## 9.11 AQ$Queue_Table_Name_S: Queue Subscribers

The `AQ$``Queue_Table_Name``_S` view provides information about subscribers for all the queues in any given queue table. It shows subscribers created by users with `DBMS_AQADM.ADD_SUBSCRIBER` and subscribers created for the apply process to apply user-created events. It also displays the transformation for the subscriber, if it was created with one. It is generated when the queue table is created.

This view provides functionality that is equivalent to the `DBMS_AQADM.QUEUE_SUBSCRIBERS()` procedure. For these queues, Oracle recommends that the view be used instead of this procedure to view queue subscribers. This view is created only for 8.1-compatible queue tables.

Table 9-11 AQ$Queue_Table_Name_S View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`QUEUE` | `VARCHAR2(30)` | `NOT NULL` | Name of queue for which subscriber is defined
`NAME` | `VARCHAR2(30)` | - | Name of agent
`ADDRESS` | `VARCHAR2(1024)` | - | Address of agent
`PROTOCOL` | `NUMBER` | - | Protocol of agent
`TRANSFORMATION` | `VARCHAR2(61)` | - | Name of the transformation (can be null)

## 9.12 AQ$Queue_Table_Name_R: Queue Subscribers and Their Rules

The `AQ$``Queue_Table_Name``_R` view displays only the subscribers based on rules for all queues in a given queue table, including the text of the rule defined by each subscriber. It also displays the transformation for the subscriber, if one was specified. It is generated when the queue table is created.

This view is created only for 8.1-compatible queue tables.

Table 9-12 AQ$Queue_Table_Name_R View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`QUEUE` | `VARCHAR2(30)` | `NOT NULL` | Name of queue for which subscriber is defined
`NAME` | `VARCHAR2(30)` | - | Name of agent
`ADDRESS` | `VARCHAR2(1024)` | - | Address of agent
`PROTOCOL` | `NUMBER` | - | Protocol of agent
`RULE` | `CLOB` | - | Text of defined rule
`RULE_SET` | `VARCHAR2(65)` | - | Set of rules
`TRANSFORMATION` | `VARCHAR2(61)` | - | Name of the transformation (can be null)

## 9.13 DBA_QUEUE_SUBSCRIBERS: All Queue Subscribers in Database

The `DBA_QUEUE_SUBSCRIBERS` view returns a list of all subscribers on all queues in the database.

Table 9-13 DBA_QUEUE_SUBSCRIBERS View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`OWNER` | `VARCHAR2(30)` | `NOT NULL` | Owner of the queue
`QUEUE_NAME` | `VARCHAR2(30)` | `NOT NULL` | Name of the queue
`QUEUE_TABLE` | `VARCHAR2(30)` | `NOT NULL` | Name of the queue table containing the queue
`CONSUMER_NAME` | `VARCHAR2(30)` | - | Name of the subscriber
`ADDRESS` | `VARCHAR2(1024)` | - | Address of the subscriber
`PROTOCOL` | `NUMBER` | - | Protocol of the subscriber
`TRANSFORMATION` | `VARCHAR2(61)` | - | Transformation for the subscriber
`DELIVERY_MODE` | `VARCHAR2(22)` | - | Message delivery mode for the subscriber, which can be `PERSISTENT`, `BUFFERED`, or `PERSISTENT_OR_BUFFERED`.
`QUEUE_TO_QUEUE` | `VARCHAR2(5)` | - | `TRUE` if the subscriber is a queue-to-queue subscriber and `FALSE` otherwise

## 9.14 USER_QUEUE_SUBSCRIBERS: Queue Subscribers in User Schema

The `USER_QUEUE_SUBSCRIBERS` view returns a list of subscribers on queues in the schema of the current user.

Table 9-14 USER_QUEUE_SUBSCRIBERS View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`QUEUE_NAME` | `VARCHAR2(30)` | `NOT NULL` | Name of the queue
`QUEUE_TABLE` | `VARCHAR2(30)` | `NOT NULL` | Name of the queue table containing the queue
`CONSUMER_NAME` | `VARCHAR2(30)` | - | Name of the subscriber
`ADDRESS` | `VARCHAR2(1024)` | - | Address of the subscriber
`PROTOCOL` | `NUMBER` | - | Protocol of the subscriber
`TRANSFORMATION` | `VARCHAR2(61)` | - | Transformation for the subscriber
`DELIVERY_MODE` | `VARCHAR2(22)` | - | Message delivery mode for the subscriber, which can be `PERSISTENT`, `BUFFERED`, or `PERSISTENT_OR_BUFFERED`.
`QUEUE_TO_QUEUE` | `VARCHAR2(5)` | - | `TRUE` if the subscriber is a queue-to-queue subscriber and `FALSE` otherwise

## 9.15 ALL_QUEUE_SUBSCRIBERS: Subscribers for Queues Where User Has Queue Privileges

The `ALL_QUEUE_SUBSCRIBERS` view returns a list of subscribers to queues that the current user has privileges to dequeue from.

Table 9-15 ALL_QUEUE_SUBSCRIBERS View

Column | Datatype | Null | Description
--- | --- | --- | ---
`OWNER` | `VARCHAR2(30)` | `NOT NULL` | Owner of the queue
`QUEUE_NAME` | `VARCHAR2(30)` | `NOT NULL` | Name of the queue
`QUEUE_TABLE` | `VARCHAR2(30)` | `NOT NULL` | Name of the queue table containing the queue
`CONSUMER_NAME` | `VARCHAR2(30)` | - | Name of the subscriber
`ADDRESS` | `VARCHAR2(1024)` | - | Address of the subscriber
`PROTOCOL` | `NUMBER` | - | Protocol of the subscriber
`TRANSFORMATION` | `VARCHAR2(61)` | - | Transformation for the subscriber
`DELIVERY_MODE` | `VARCHAR2(22)` | - | Message delivery mode for the subscriber, which can be `PERSISTENT`, `BUFFERED`, or `PERSISTENT_OR_BUFFERED`.
`QUEUE_TO_QUEUE` | `VARCHAR2(5)` | - | `TRUE` if the subscriber is a queue-to-queue subscriber and `FALSE` otherwise

## 9.16 GV$AQ: Number of Messages in Different States in Database

The `GV$AQ` view provides information about the number of messages in different states for the whole database.

In a Real Application Clusters environment, each instance keeps its own Oracle Streams AQ statistics information in its own System Global Area (SGA), and does not have knowledge of the statistics gathered by other instances. When a `GV$AQ` view is queried by an instance, all other instances funnel their Oracle Streams AQ statistics information to the instance issuing the query.

Table 9-16 GV$AQ View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`QID` | `NUMBER` | - | Identity of the queue (same as `QID` in `USER_QUEUES` and `DBA_QUEUES`)
`WAITING` | `NUMBER` | - | Number of messages in the state `WAITING`
`READY` | `NUMBER` | - | Number of messages in state `READY`
`EXPIRED` | `NUMBER` | - | Number of messages in state `EXPIRED`
`TOTAL_WAIT` | `NUMBER` | - | Number of seconds messages in the queue have been waiting in state `READY`
`AVERAGE_WAIT` | `NUMBER` | - | Average number of seconds messages in state `READY` have been waiting to be dequeued

## 9.17 V$AQ: Number of Messages in Different States for Specific Instances

The `V$AQ` view provides information about the number of messages in different states for specific instances.

Table 9-17 V$AQ View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`QID` | `NUMBER` | - | Identity of the queue (same as `QID` in `USER_QUEUES` and `DBA_QUEUES`)
`WAITING` | `NUMBER` | - | Number of messages in the state `WAITING`
`READY` | `NUMBER` | - | Number of messages in state `READY`
`EXPIRED` | `NUMBER` | - | Number of messages in state `EXPIRED`
`TOTAL_WAIT` | `NUMBER` | - | Number of seconds messages in the queue have been waiting in state `READY`
`AVERAGE_WAIT` | `NUMBER` | - | Average number of seconds messages in state `READY` have been waiting to be dequeued

## 9.18 AQ$INTERNET_USERS: Oracle Streams AQ Agents Registered for Internet Access

The `AQ$INTERNET_USERS` view provides information about the agents registered for Internet access to Oracle Streams AQ. It also provides the list of database users that each Internet agent maps to.

Table 9-18 AQ$INTERNET_USERS View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`AGENT_NAME` | `VARCHAR2(30)` | - | Name of the Oracle Streams AQ Internet agent
`DB_USERNAME` | `VARCHAR2(30)` | - | Name of database user that this Internet agent maps to
`HTTP_ENABLED` | `VARCHAR2(4)` | - | Indicates whether this agent is allowed to access Oracle Streams AQ through HTTP (`YES` or `NO`)
`FTP_ENABLED` | `VARCHAR2(4)` | - | Indicates whether this agent is allowed to access Oracle Streams AQ through FTP (always `NO` in current release)

## 9.19 DBA_TRANSFORMATIONS: All Transformations

The `DBA_TRANSFORMATIONS` view displays all the transformations in the database. These transformations can be specified with Advanced Queue operations like enqueue, dequeue and subscribe to automatically integrate transformations in messaging. This view is accessible only to users having DBA privileges.

Table 9-19 DBA_TRANSFORMATIONS View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`TRANSFORMATION_ID` | `NUMBER` | `NOT NULL` | Unique ID for the transformation
`OWNER` | `VARCHAR2(30)` | `NOT NULL` | Owning user of the transformation
`NAME` | `VARCHAR2(30)` | `NOT NULL` | Transformation name
`FROM_TYPE` | `VARCHAR2(61)` | - | Source type name
`TO_TYPE` | `VARCHAR2(91)` | - | Target type name

## 9.20 DBA_ATTRIBUTE_TRANSFORMATIONS: All Transformation Functions

The `DBA_ATTRIBUTE_TRANSFORMATIONS` view displays the transformation functions for all the transformations in the database.

Table 9-20 DBA_ATTRIBUTE_TRANSFORMATIONS View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`TRANSFORMATION_ID` | `NUMBER` | `NOT NULL` | Unique ID for the transformation
`OWNER` | `VARCHAR2(30)` | `NOT NULL` | Transformation owner
`NAME` | `VARCHAR2(30)` | `NOT NULL` | Transformation name
`FROM_TYPE` | `VARCHAR2(61)` | - | Source type name
`TO_TYPE` | `VARCHAR2(91)` | - | Target type name
`ATTRIBUTE` | `NUMBER` | `NOT NULL` | Target type attribute number
`ATTRIBUTE_TRANSFORMATION` | `VARCHAR2(4000)` | - | Transformation function for the attribute

## 9.21 USER_TRANSFORMATIONS: User Transformations

The `USER_TRANSFORMATIONS` view displays all the transformations owned by the user. To view the transformation definition, query `USER_ATTRIBUTE_TRANSFORMATIONS`.

Table 9-21 USER_TRANSFORMATIONS View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`TRANSFORMATION_ID` | `NUMBER` | `NOT NULL` | Unique ID for the transformation
`NAME` | `VARCHAR2(30)` | `NOT NULL` | Transformation name
`FROM_TYPE` | `VARCHAR2(61)` | - | Source type name
`TO_TYPE` | `VARCHAR2(91)` | - | Target type name

## 9.22 USER_ATTRIBUTE_TRANSFORMATIONS: User Transformation Functions

The `USER_ATTRIBUTE_TRANSFORMATIONS` view displays the transformation functions for all the transformations of the user.

Table 9-22 USER_ATTRIBUTE_TRANSFORMATIONS View

Column | Datatype | NULL | Description
--- | --- | --- | ---
`TRANSFORMATION_ID` | `NUMBER` | `NOT NULL` | Unique ID for the transformation
`NAME` | `VARCHAR2(30)` | `NOT NULL` | Transformation name
`FROM_TYPE` | `VARCHAR2(61)` | - | Source type name
`TO_TYPE` | `VARCHAR2(91)` | - | Target type name
`ATTRIBUTE` | `NUMBER` | `NOT NULL` | Target type attribute number
`ATTRIBUTE_TRANSFORMATION` | `VARCHAR2(4000)` | - | Transformation function for the attribute